select 
t.name as table_name,
t.type as table_type,
t.object_id as table_id,
col.name as column_name, 
col.column_id, 
st.name as user_type_name, 
schema_name(st.schema_id) as user_type_schema, 
(case when st.name IN ('char', 'varchar','nchar','nvarchar') then (CASE WHEN col.max_length=-1 THEN 2147483647 ELSE CONVERT(int, CASE WHEN t.name IN ('nchar','nvarchar') THEN  col.max_length/2 ELSE col.max_length END) end) end) as max_length,
(case WHEN st.name IN ('decimal','numeric') THEN col.precision ELSE null END) as precision, 
(case WHEN st.name IN ('decimal','numeric') THEN col.scale ELSE null END) as scale, 
bt.name as system_type_name, 
col.collation_name, 
col.is_nullable, 
col.is_ansi_padded, 
col.is_rowguidcol, 
col.is_identity, 
case when(idc.column_id is null) then null else CONVERT(nvarchar(40), idc.seed_value) end as identity_seed, 
case when(idc.column_id is null) then null else CONVERT(nvarchar(40), idc.increment_value) end as identity_increment, 
CONVERT(bit, case when(cmc.column_id is null) then 0 else 1 end) as is_computed, 
case when(cmc.column_id is null) then null else cmc.definition end as computed_text, 
case when(cmc.column_id is null) then null else cmc.is_persisted end as is_persisted, 
convert(bit, ColumnProperty(col.object_id, col.name, N'IsIdNotForRepl')) as is_identity_not_for_replication, 
CONVERT(bit, case when (select COUNT(*) from sys.foreign_key_columns fkc 
inner JOIN sys.foreign_keys fk ON fk.object_id = fkc.constraint_object_id
where fkc.parent_object_id = col.object_id and fkc.parent_column_id = col.column_id)>0 then 1 else 0 end) as is_foreign_key,
col.is_replicated, 
col.is_non_sql_subscribed, 
col.is_merge_published, 
col.is_dts_replicated, 
col.rule_object_id, 
robj.name as rule_name, 
schema_name(robj.schema_id) as rule_schema, 
col.default_object_id,
OBJECTPROPERTY(col.default_object_id, N'IsDefaultCnst') as is_default_constraint, 
dobj.name as def_name, 
defCst.definition as default_value, 
schema_name(dobj.schema_id) as def_schema, 
CONVERT(bit, case when (ftc.column_id is null) then 0 else 1 end) as is_full_text_indexed, 
col_name(col.object_id, ftc.type_column_id) full_text_column_name, 
ftc.language_id as FT_language_id, 
COLUMNPROPERTY(col.object_id, col.name, 'IsDeterministic') as IsDeterministic, 
xmlcoll.name as xmlSchema_name, 
schema_name(xmlcoll.schema_id) as xmlSchema_schema, 
col.is_xml_document, 
col.is_sparse, 
col.is_column_set,
CONVERT(bit, case when (t.type <> 'U' and col.is_computed = 1) then 1 else 0 end) as is_readonly
from sys.columns col 
left outer join sys.tables as t on col.object_id=t.object_id
left outer join sys.types st on st.user_type_id = col.user_type_id 
left outer join sys.types bt on bt.user_type_id = col.system_type_id 
left outer join sys.objects robj on robj.object_id = col.rule_object_id and robj.type = 'R' 
left outer join sys.objects dobj on dobj.object_id = col.default_object_id and dobj.type = 'D' 
left outer join sys.default_constraints defCst on defCst.parent_object_id = col.object_id and defCst.parent_column_id = col.column_id 
left outer join sys.identity_columns idc on idc.object_id = col.object_id and idc.column_id = col.column_id 
left outer join sys.computed_columns cmc on cmc.object_id = col.object_id and cmc.column_id = col.column_id
left outer join sys.fulltext_index_columns ftc on ftc.object_id = col.object_id and ftc.column_id = col.column_id 
left outer join sys.xml_schema_collections xmlcoll on xmlcoll.xml_Collection_id = col.xml_Collection_id 

where col.object_id = t.object_id
order by t.name, col.column_id